/**
 * @author 0283000115
 * @date 2018年3月5日-上午11:09:11
 */
package com.zte.claa.inficombo.csa.app.dao.mysql.jdbcdao;

import java.util.List;

import com.alibaba.druid.pool.DruidDataSource;

/**
 * @author 0283000115
 *
 */
public class ResIndexDataDao extends AppDataDao {
	// date format
	// public static final SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");	
		
	public static final String TPL_PDB = "_PROJ_DB_NAME_";
	public static final String TPL_TAB = "_TABLE_NAME_";
	public static final String TPL_DEVPARA = "_DEVEUI_PARA_";
	public static final String TPL_MSGTPFIELD = "_MSG_TP_FIELD_";
	// 根据关键字段查询（主要用于趋势分析）
	public static final String TPL_KEYFIELD = "_MSG_KEY_FIELD_";
	
	// 1.普通查询
	public static String QUERY_SQL = "SELECT t.*, d.resname as devname FROM " +
									 TPL_PDB + "."+TPL_TAB+" t, combodb.t_combo_res_shadow d "+
									 " WHERE t.deveui = d.resid "+
									 "   AND t.collecttime >= ? "+
									 "   AND t.collecttime <= ? "+
									 " ORDER BY t.collecttime DESC "+
									 " LIMIT ? OFFSET ? ";
	
	// 2.指定deveui列表查询
	public static String QUERY_SQL_DEV = "SELECT t.*, d.resname as devname FROM " +
										 TPL_PDB + "."+TPL_TAB+" t, combodb.t_combo_res_shadow d "+
										 " WHERE t.deveui = d.resid "+
										 "   AND t.collecttime >= ? "+
										 "   AND t.collecttime <= ? "+
										 "   AND t.deveui IN ("+TPL_DEVPARA+") "+
										 " ORDER BY t.collecttime DESC "+
										 " LIMIT ? OFFSET ? ";
	
	// 3.按最新时间查询每一个deveui对应的数据
	public static String QUERY_SQL_LATESTDATA = "SELECT t.*, d.resname as devname FROM combodb.t_combo_res_shadow d, " + 
										TPL_PDB + "."+TPL_TAB+" t " + " "+
										" JOIN (SELECT MAX(collecttime) AS maxtime, deveui FROM "+ " " +
										       TPL_PDB + "."+TPL_TAB + " "+
										       " WHERE collecttime >= ? " + 
										       " GROUP BY deveui) tt ON t.deveui = tt.deveui "+ 
										       " AND t.collecttime = tt.maxtime " +
										" WHERE t.deveui IN ("+TPL_DEVPARA+") "+
										"   AND t.deveui = d.resid " + 
										" ORDER BY t.collecttime DESC ";
	
	// 4.按最新时间查询每一个deveui下不同msgtype对应的数据（主要用于油田设备的最新坐标查询）
	public static String QUERY_SQL_LATESTDATA_BYMSGTYPE = "SELECT t.*, d.resname as devname FROM combodb.t_combo_res_shadow d, "+
										TPL_PDB + "."+TPL_TAB+" t " + " " +
										" JOIN (SELECT MAX(collecttime) AS maxtime, deveui, "+TPL_MSGTPFIELD+" " + 
										       " FROM "+ TPL_PDB + "."+TPL_TAB + " "+
										       " WHERE collecttime >= ? " + 
										       " GROUP BY deveui, "+TPL_MSGTPFIELD+") tt ON t.deveui = tt.deveui "+ 
										       " AND t."+TPL_MSGTPFIELD+" = tt."+TPL_MSGTPFIELD+" " +
										       " AND t.collecttime = tt.maxtime " +
										" WHERE t.deveui IN ("+TPL_DEVPARA+") "+
										"   AND t.deveui = d.resid " + 
										" ORDER BY t.collecttime DESC ";

	// 5.指定deveui和keyfield字段查询 （统计分析）
	public static String QUERY_SQL_DEV_KV = "SELECT t.*, d.resname as devname FROM " +
										 TPL_PDB + "."+TPL_TAB+" t, combodb.t_combo_res_shadow d "+
										 " WHERE t.deveui = d.resid "+
										 "   AND t.collecttime >= ? "+
										 "   AND t.collecttime <= ? "+
										 "   AND t.deveui IN ("+TPL_DEVPARA+") "+
										 "   AND t."+TPL_KEYFIELD+" = ? " +
										 " ORDER BY t.collecttime DESC "+
										 " LIMIT ? OFFSET ? ";
		
	// 日志管理器
	// private Logger logger = LoggerFactory.getLogger(ResIndexDataDao.class);
	
	/**
	 * 构建器
	 * 
	 * @param dataSource
	 */
	public ResIndexDataDao(DruidDataSource dataSource){
		super(dataSource);
	}
	
	/**
	 * 
	 * @param projectId
	 * @return
	 */
	public String getProjDBName(String projectId){
		return "db_"+projectId.toLowerCase().trim();
	}
	
	/**
	 * 准备SQL
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSql(AppDataQueryPara para, String tabName){
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = "";
		if(devEuis == null || devEuis.size() == 0){
			qrySql = QUERY_SQL.replaceAll(TPL_PDB, pdbName);
			qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		} else {
			qrySql = QUERY_SQL_DEV.replaceAll(TPL_PDB, pdbName);
			qrySql = qrySql.replaceAll(TPL_TAB, tabName);
			StringBuffer sb = new StringBuffer();
			for(int i=0; i<devEuis.size(); i++){
				if(i > 0){
					sb.append(",");
				}
				sb.append("'").append(devEuis.get(i)).append("'");
			}
			qrySql = qrySql.replaceAll(TPL_DEVPARA, sb.toString());
		}
		return qrySql;
	}
	
	/**
	 * 准备SQL
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSql4LatestQry(AppDataQueryPara para, String tabName) {
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = QUERY_SQL_LATESTDATA.replaceAll(TPL_PDB, pdbName);
		qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < devEuis.size(); i++) {
			if (i > 0) {
				sb.append(",");
			}
			sb.append("'").append(devEuis.get(i)).append("'");
		}
		return qrySql.replaceAll(TPL_DEVPARA, sb.toString());
	}
	
	/**
	 * 准备SQL
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSql4LatestQryWithKeyField(AppDataQueryPara para, String tabName, String keyFieldName) {
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = QUERY_SQL_LATESTDATA_BYMSGTYPE.replaceAll(TPL_PDB, pdbName);
		qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		qrySql = qrySql.replaceAll(TPL_MSGTPFIELD, keyFieldName);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < devEuis.size(); i++) {
			if (i > 0) {
				sb.append(",");
			}
			sb.append("'").append(devEuis.get(i)).append("'");
		}
		return qrySql.replaceAll(TPL_DEVPARA, sb.toString());
	}
	
	/**
	 * 准备按deveui和keyfield的查询SQL 
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSqlWithKeyField(AppDataQueryPara para, String tabName){
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = QUERY_SQL_DEV_KV.replaceAll(TPL_PDB, pdbName);
		qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		qrySql = qrySql.replaceAll(TPL_KEYFIELD, para.getKeyFieldName());
		StringBuffer sb = new StringBuffer();
		for(int i=0; i<devEuis.size(); i++){
			if(i > 0){
				sb.append(",");
			}
			sb.append("'").append(devEuis.get(i)).append("'");
		}
		return qrySql.replaceAll(TPL_DEVPARA, sb.toString());
	}

}
